Build the Fact Table (the query)

If you have not done so already, please review the previous topic before starting this topic.

  1. On the Fact Table screen of the Build OLAP Cube dialog, click on the Build Query build query icon icon at the lower left to launch the Build Query dialog box that you have seen in previous examples.  
    This shows the dialog box where the fact table is created.
  1. Build the query using the Employees, Locations, Regions, Orders, Order_Details, and Products tables. 
  2. Join the tables as follows:
    • Employees.emp_id with Orders.employee-id
    • Employees.location_id with Locations.location_id
    • Locations.region_id with Regions.region_id
    • Orders.transaction_id with Order_Details.transaction_id
    • Order_Details.product_id with Product.product_id

Your tables should look like the following:

Table joins for the olap cube.

  1. In the SELECT tab, enter the following fields by double-clicking them within their tables: Employees.last_name, Orders.sale_date, Products.product_name, and Regions.region_name.
  2. In the last column, select <calculated> in the Table field.
  3. Click the ellipses button ellipses button to launch the SQL Editor.
  4. Multiply the unit price by the quantity to get the total sales in the <calculated> column:
    Products.unit_price *Order_Details.quantity
  5. Click OK to return to the Build Query editor.
  6. Enter 'sales_total' in the As field in the <calculated> column.
  7. Your SELECT clause should now look like this:
    The select clause of the build query for the olap cube. The fields described in steps 3 through 8 have been filled in.

To set a condition to find only records where the sale_date is greater than the date input by the user:

  1. Click the WHERE tab.
  2. In the Orders table, double-click the sale_date field to autofill the query fields.
  3. Click the ellipses button ellipses button in the Conditions field to launch the SQL Editor, and enter > (greater than) and click the user-defined variable button variable button and select the date object you placed on your dashboard.
  4. Click OK to return to the Build Query editor.
  5. Click OK again to return to the OLAP cube builder.
  6. Click Next to continue.